﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

using VK.Comm;
using VK.Comm.TransactSql;

namespace VK.Casemeet.Lines
{
    internal static class LineMemberDAL
    {
        internal static LineMemberEntity GetLineMemberByReader(IDataReader dr)
        {
            LineMemberEntity lineMember = new LineMemberEntity();

            lineMember.LineMemberID = dr.IsDBNull(0) ? 0 : dr.GetInt32(0);
            lineMember.MemberID = dr.IsDBNull(1) ? 0 : dr.GetInt32(1);
            lineMember.LineID = dr.IsDBNull(2) ? 0 : dr.GetInt32(2);
            lineMember.Created = Util.ConverDateTime(dr.GetValue(3));
            lineMember.MemberName = dr.IsDBNull(4) ? null : dr.GetString(4);
            lineMember.Sex = dr.IsDBNull(5) ? 0 : dr.GetInt32(5);
            lineMember.QQ = dr.IsDBNull(6) ? null : dr.GetString(6);
            lineMember.Phone = dr.IsDBNull(7) ? null : dr.GetString(7);
            lineMember.Area = dr.IsDBNull(8) ? null : dr.GetString(8);
            lineMember.Address = dr.IsDBNull(9) ? null : dr.GetString(9);
            lineMember.LineName = dr.IsDBNull(10) ? null : dr.GetString(10);

            return lineMember;
        }

        internal static int GetLineMemberCount(IList<Condition> conditionArray)
        {
            Database db = DatabaseFactory.CreateDatabase();
            int result = 0;

            string SelectLineMember = @"SELECT COUNT(LineMember.[LineMemberID]) 
                FROM LineMember INNER JOIN 
                          Line ON LineMember.LineID = Line.LineID INNER JOIN 
                          Member ON LineMember.MemberID = Member.MemberID LEFT OUTER JOIN
                          Area AS CityArea ON Member.CityID = CityArea.AreaID LEFT OUTER JOIN
                          Area AS AreaArea ON Member.AreaID = AreaArea.AreaID LEFT OUTER JOIN
                          Area AS ProvinceArea ON Member.ProvinceID = ProvinceArea.AreaID ";

            List<DbParameter> parameterlst = new List<DbParameter>();
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectLineMember, ref parameterlst);
            DbCommand SelectLineMemberCommand = db.GetSqlStringCommand(SelectLineMember);
            DbHelper.PackDbCommandByDbParameters(SelectLineMemberCommand, parameterlst.ToArray());

            object obj = db.ExecuteScalar(SelectLineMemberCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        internal static List<LineMemberEntity> GetLineMemberList(int startRecordIndex, int endRecordIndex, IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<LineMemberEntity> lineMemberlst = new List<LineMemberEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectLineMember = @"
                    SELECT [LineMemberID],[MemberID],[LineID],[Created],
                        [MemberName],[Sex],[QQ],[Phone],[Area],[Address],[LineName]
                    FROM (
                        SELECT LineMember.LineMemberID, LineMember.MemberID, LineMember.LineID, 
                        LineMember.Created, Member.Name AS MemberName, Member.Sex, Member.QQ, 
                        Member.Phone, ProvinceArea.Name+' '+CityArea.Name+' '+AreaArea.Name AS Area, Member.Address, 
                        Line.Name AS LineName, ROW_NUMBER() OVER (ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectLineMember += " " + sortField + " " + sortMode + ",";
            SelectLineMember += @" [LineMember].[LineMemberID] Desc
                    ) AS rowNum 
                        FROM LineMember INNER JOIN
                          Line ON LineMember.LineID = Line.LineID INNER JOIN 
                          Member ON LineMember.MemberID = Member.MemberID LEFT OUTER JOIN
                          Area AS CityArea ON Member.CityID = CityArea.AreaID LEFT OUTER JOIN
                          Area AS AreaArea ON Member.AreaID = AreaArea.AreaID LEFT OUTER JOIN
                          Area AS ProvinceArea ON Member.ProvinceID = ProvinceArea.AreaID ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectLineMember, ref parameterlst);
            SelectLineMember += @"
                    ) AS [LineMember] 
                    WHERE rowNum BETWEEN @StartRecordIndex and @EndRecordIndex ";

            DbCommand SelectLineMemberCommand = db.GetSqlStringCommand(SelectLineMember);
            parameterlst.Add(DbHelper.PackInParameter(db, "@StartRecordIndex", DbType.Int32, startRecordIndex));
            parameterlst.Add(DbHelper.PackInParameter(db, "@EndRecordIndex", DbType.Int32, endRecordIndex));
            DbHelper.PackDbCommandByDbParameters(SelectLineMemberCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectLineMemberCommand))
            {
                while (dr.Read())
                {
                    lineMemberlst.Add(GetLineMemberByReader(dr));
                }
            }

            return lineMemberlst;
        }

        internal static List<LineMemberEntity> GetLineMemberList(IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<LineMemberEntity> lineMemberlst = new List<LineMemberEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectLineMember = @"
                    SELECT LineMember.LineMemberID, LineMember.MemberID, LineMember.LineID, 
                        LineMember.Created, Member.Name AS MemberName, Member.Sex, Member.QQ, 
                        Member.Phone, ProvinceArea.Name+' '+CityArea.Name+' '+AreaArea.Name AS Area, Member.Address, Line.Name AS LineName
                    FROM LineMember INNER JOIN
                          Line ON LineMember.LineID = Line.LineID INNER JOIN 
                          Goods ON LineMember.LineID = Goods.LineID INNER JOIN
                          Member ON LineMember.MemberID = Member.MemberID LEFT OUTER JOIN
                          Area AS CityArea ON Member.CityID = CityArea.AreaID LEFT OUTER JOIN
                          Area AS AreaArea ON Member.AreaID = AreaArea.AreaID LEFT OUTER JOIN
                          Area AS ProvinceArea ON Member.ProvinceID = ProvinceArea.AreaID ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectLineMember, ref parameterlst);
            SelectLineMember += " ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectLineMember += " " + sortField + " " + sortMode + ",";
            SelectLineMember += " [LineMember].[LineMemberID] Desc ";

            DbCommand SelectLineMemberCommand = db.GetSqlStringCommand(SelectLineMember);
            DbHelper.PackDbCommandByDbParameters(SelectLineMemberCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectLineMemberCommand))
            {
                while (dr.Read())
                {
                    lineMemberlst.Add(GetLineMemberByReader(dr));
                }
            }

            return lineMemberlst;
        }

        internal static LineMemberEntity GetLineMember(int reserveID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            LineMemberEntity lineMember = null;

            string SelectLineMember = @"
                    SELECT LineMember.LineMemberID, LineMember.MemberID, LineMember.LineID, 
                        LineMember.Created, Member.Name AS MemberName, Member.Sex, Member.QQ, 
                        Member.Phone, ProvinceArea.Name+' '+CityArea.Name+' '+AreaArea.Name AS Area, Member.Address, Line.Name AS LineName
                    FROM LineMember INNER JOIN 
                          Line ON LineMember.LineID = Line.LineID INNER JOIN 
                          Member ON LineMember.MemberID = Member.MemberID LEFT OUTER JOIN
                          Area AS CityArea ON Member.CityID = CityArea.AreaID LEFT OUTER JOIN
                          Area AS AreaArea ON Member.AreaID = AreaArea.AreaID LEFT OUTER JOIN
                          Area AS ProvinceArea ON Member.ProvinceID = ProvinceArea.AreaID
                    WHERE [LineMember].[LineMemberID]=@LineMemberID
                ";

            DbCommand SelectLineMemberCommand = db.GetSqlStringCommand(SelectLineMember);
            db.AddInParameter(SelectLineMemberCommand, "@LineMemberID", DbType.Int32, reserveID);

            using (IDataReader dr = db.ExecuteReader(SelectLineMemberCommand))
            {
                if (dr.Read())
                {
                    lineMember = GetLineMemberByReader(dr);
                }
            }

            return lineMember;
        }

        internal static int InsertLineMember(LineMemberEntity reservegoods)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string InsertLineMember = @"
            IF NOT EXISTS(SELECT LineMemberID FROM LineMember WHERE LineID=@LineID AND MemberID=@MemberID)
		    BEGIN
                INSERT INTO [LineMember] ([MemberID],[LineID]) VALUES (@MemberID,@LineID);
            END
            ";

            DbCommand InsertLineMemberCommand = db.GetSqlStringCommand(InsertLineMember);
            db.AddInParameter(InsertLineMemberCommand, "@MemberID", DbType.Int32, reservegoods.MemberID);
            db.AddInParameter(InsertLineMemberCommand, "@LineID", DbType.Int32, reservegoods.LineID);

            result = db.ExecuteNonQuery(InsertLineMemberCommand);

            return result;
        }

        internal static int UpdateLineMember(LineMemberEntity reservegoods)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();
            string UpdateLineMember = @"UPDATE [LineMember] 
				 SET [MemberID]=@MemberID,[LineID]=@LineID,[Created]=@Created 
				 WHERE [LineMemberID]=@LineMemberID";

            DbCommand UpdateLineMemberCommand = db.GetSqlStringCommand(UpdateLineMember);
            db.AddInParameter(UpdateLineMemberCommand, "@LineMemberID", DbType.Int32, reservegoods.LineMemberID);
            db.AddInParameter(UpdateLineMemberCommand, "@MemberID", DbType.Int32, reservegoods.MemberID);
            db.AddInParameter(UpdateLineMemberCommand, "@LineID", DbType.Int32, reservegoods.LineID);
            db.AddInParameter(UpdateLineMemberCommand, "@Created", DbType.DateTime, reservegoods.Created);

            result = db.ExecuteNonQuery(UpdateLineMemberCommand);

            return result;
        }

        internal static int DeleteLineMember(int reserveID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string DeleteLineMember = @"
                DELETE FROM [LineMember] WHERE LineMemberID=@LineMemberID";

            DbCommand DeleteLineMemberCommand = db.GetSqlStringCommand(DeleteLineMember);
            db.AddInParameter(DeleteLineMemberCommand, "@LineMemberID", DbType.Int32, reserveID);
            result = db.ExecuteNonQuery(DeleteLineMemberCommand);

            return result;
        }

        internal static int DeleteLineMemberByMemberID(int memberID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string DeleteLineMember = @"
                DELETE FROM [LineMember] WHERE MemberID=@MemberID";

            DbCommand DeleteLineMemberCommand = db.GetSqlStringCommand(DeleteLineMember);
            db.AddInParameter(DeleteLineMemberCommand, "@MemberID", DbType.Int32, memberID);
            result = db.ExecuteNonQuery(DeleteLineMemberCommand);

            return result;
        }

        internal static bool IsLineMember(int goodsID, int memberID)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string InsertLineMember = @"Declare @IsLineMember bit;
            IF EXISTS(SELECT LineMemberID FROM LineMember WHERE LineID=@LineID AND MemberID=@MemberID)
		    BEGIN
                SET @IsLineMember=1;
            END
            ELSE
            BEGIN
                SET @IsLineMember=0;
            END
            SELECT @IsLineMember;
            ";

            DbCommand InsertLineMemberCommand = db.GetSqlStringCommand(InsertLineMember);
            db.AddInParameter(InsertLineMemberCommand, "@MemberID", DbType.Int32, memberID);
            db.AddInParameter(InsertLineMemberCommand, "@LineID", DbType.Int32, goodsID);

            object obj = db.ExecuteScalar(InsertLineMemberCommand);

            return obj == null ? false : (bool)obj;
        }
    }
}
